package com.poi.demopoi.service.impl;

import com.poi.demopoi.controller2.MesExceptionBillzDTO;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.List;

@Service
public class MesServiceImpl implements MesService {


    @Override
    public void exportData(int sheetNum, String sheetTitle, String[] headers,
                           List<MesExceptionBillzDTO> resultSend) {
        try {

            String filePath = "E:\\000temp\\test4.xlsx";
            File file = new File(filePath);
            XSSFWorkbook workbook1 = new XSSFWorkbook(new FileInputStream(file));
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);

            // 生成一个表格
//            Sheet sheet = sxssfWorkbook.createSheet();
            Sheet sheet = sxssfWorkbook.getSheetAt(0);

            /* *********** ***********第一行标题start*************************** */
            // 产生表格标题行 单元格范围 参数（int firstRow, int lastRow, int firstCol, intlastCol)
            //这里设置标题单元格的合并范围，0,0,4,6代表第0行的4-6列合并为一列，0,1,0,0代表第0列的第0行到第1行合并为一行
            CellRangeAddress region1 = new CellRangeAddress(0, 0, 4, 6);//第0行的第4-6列合并
            CellRangeAddress region2 = new CellRangeAddress(0, 1, 7, 7);//第1行的第7列合并
            CellRangeAddress region3 = new CellRangeAddress(0, 1, 8, 8);
            CellRangeAddress region4 = new CellRangeAddress(0, 1, 9, 9);
            CellRangeAddress region5 = new CellRangeAddress(0, 1, 10, 10);
            CellRangeAddress region6 = new CellRangeAddress(0, 1, 11, 11);
            CellRangeAddress region7 = new CellRangeAddress(0, 1, 12, 12);
            CellRangeAddress region8 = new CellRangeAddress(0, 1, 0, 0);//第0行到第1行合并为一行
            CellRangeAddress region9 = new CellRangeAddress(0, 1, 1, 1);//第0行到第2行合并为一行
            CellRangeAddress region10 = new CellRangeAddress(0, 1, 2, 2);//第0行到第3行合并为一行
            CellRangeAddress region11 = new CellRangeAddress(0, 1, 3, 3);//第0行到第4行合并为一行
//            CellRangeAddress region11 = new CellRangeAddress(2, 2, 0, 0);//第3行到第1列合并为一行

            // 在sheet里将表头单元格合并
            sheet.addMergedRegion(region1);
            sheet.addMergedRegion(region2);
            sheet.addMergedRegion(region3);
            sheet.addMergedRegion(region4);
            sheet.addMergedRegion(region5);
            sheet.addMergedRegion(region6);
            sheet.addMergedRegion(region7);
            sheet.addMergedRegion(region8);
            sheet.addMergedRegion(region9);
            sheet.addMergedRegion(region10);
            sheet.addMergedRegion(region11);

            // 设置sheet名字
            sxssfWorkbook.setSheetName(sheetNum, "异常问题导出");
            /* **********************第二行标题start*************************** */
            // 表格第二行标题
            Row headTitleSecond = sheet.createRow(0);
            // 设置样式
            CellStyle secondRowcellStyle = sxssfWorkbook.createCellStyle();
            secondRowcellStyle.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色
            //
//            secondRowcellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
//            secondRowcellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//            secondRowcellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//            secondRowcellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//            secondRowcellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//            secondRowcellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//            secondRowcellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            Font font = sxssfWorkbook.createFont();
            font.setFontName("宋体");
            font.setFontHeight((short) 220);
//            font.setBoldweight((short) 700);
            secondRowcellStyle.setFont(font);
            Cell cel = headTitleSecond.createCell(0);
            cel.setCellStyle(secondRowcellStyle);
            cel.setCellValue("异常类型");
            // 设置样式
            Cell cel1 = headTitleSecond.createCell(1);
            cel1.setCellStyle(secondRowcellStyle);
            cel1.setCellValue("产品项目");
            Cell ce2 = headTitleSecond.createCell(2);
            ce2.setCellStyle(secondRowcellStyle);
            ce2.setCellValue("异常标题");
            Cell cel3 = headTitleSecond.createCell(3);
            cel3.setCellStyle(secondRowcellStyle);
            cel3.setCellValue("异常内容");
            Cell cel4 = headTitleSecond.createCell(4);
            cel4.setCellStyle(secondRowcellStyle);
            cel4.setCellValue("异常回复");
            Cell cel5 = headTitleSecond.createCell(7);
            cel5.setCellStyle(secondRowcellStyle);
            cel5.setCellValue("是否归零");
            Cell cel6 = headTitleSecond.createCell(8);
            cel6.setCellStyle(secondRowcellStyle);
            cel6.setCellValue("发布人");
            Cell cel7 = headTitleSecond.createCell(9);
            cel7.setCellStyle(secondRowcellStyle);
            cel7.setCellValue("发布时间");
            Cell cel8 = headTitleSecond.createCell(10);
            cel8.setCellStyle(secondRowcellStyle);
            cel8.setCellValue("负责人");
            Cell cel9 = headTitleSecond.createCell(11);
            cel9.setCellStyle(secondRowcellStyle);
            cel9.setCellValue("设计人员");
            Cell cel10 = headTitleSecond.createCell(12);
            cel10.setCellStyle(secondRowcellStyle);
            cel10.setCellValue("是否有附件");

            // 表格第二行标题
            Row headTitle = sheet.createRow(1);
            Cell cel116 = headTitle.createCell(4);
            cel116.setCellStyle(secondRowcellStyle);
            cel116.setCellValue("回复内容");
            Cell cel117 = headTitle.createCell(5);
            cel117.setCellStyle(secondRowcellStyle);
            cel117.setCellValue("回复人");
            Cell cel118 = headTitle.createCell(6);
            cel118.setCellStyle(secondRowcellStyle);
            cel118.setCellValue("回复时间");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Row row = null;
            String curid = "";
            String lastid = "";

            // 遍历集合数据，产生数据行
            if (resultSend != null) {
                int index = 2;
                int begin = 2;
                int end = 2;
                for (int i = 0; i < resultSend.size(); i++) {
                    row = sheet.createRow(index);
                    MesExceptionBillzDTO dto = resultSend.get(i);
                    curid = dto.getId();
                    if (i != 0) {
                        lastid = resultSend.get(i - 1).getId();
                    } else {
                        lastid = curid;
                    }
                    row.setHeightInPoints(22.0f);

                    row.createCell(0).setCellValue((dto.getTypeName() != null) ? dto.getTypeName() : "");
                    row.createCell(1).setCellValue((dto.getTypeNameL() != null) ? dto.getTypeNameL() : "");
                    row.createCell(2).setCellValue((dto.getExTittle() != null) ? dto.getExTittle() : "");
                    row.createCell(3).setCellValue((dto.getExDescribeInfo() != null) ? dto.getExDescribeInfo() : "");
                    row.createCell(4).setCellValue((dto.getExReplyInfo() != null) ? dto.getExReplyInfo() : "");
                    row.createCell(5).setCellValue((dto.getReplierName() != null) ? dto.getReplierName() : "");
                    row.createCell(6).setCellValue((dto.getLastUpdateDate() != null) ? dto.getLastUpdateDate() : "");
//                    row.createCell(6).setCellValue((dto.getLastUpdateDate() != null) ? sdf.format(dto.getLastUpdateDate()) : "");

//                    row.createCell(7).setCellValue((dto.getStatus() != null) ? (dto.getStatus().equals("N") ? "未归零" : "已归零") : "");
//                    row.createCell(8).setCellValue(sysUserAPI.getSysUserNameById((dto.getCreatedBy() != null) ? dto.getCreatedBy() : ""));
//                    row.createCell(9).setCellValue(sdf.format((dto.getCreationDate() != null) ? dto.getCreationDate() : ""));
//                    row.createCell(10).setCellValue((dto.getDutyManNames() != null) ? dto.getDutyManNames() : "");
//                    row.createCell(11).setCellValue(sysUserAPI.getSysUserNameById((dto.getDesignerNames() != null) ? dto.getDesignerNames() : ""));
//                    row.createCell(12).setCellValue(dto.getCountAttachment() > 0 ? "是" : "否");
                    if (lastid.equals(curid)) {
                        if (i != 0) {
                            end += 1;
                        }
                    } else {
                        for (int a = 0; a < 13; a++) {
                            if (a < 4 || a > 6) {
                                // 合并  new CellRangeAddress(2, 2, 0, 0);//第3行到第1列合并为一行
                                //合并单元格时 报 java.lang.IllegalArgumentException: Merged region A1 must contain 2 or more cells？
                                //解决方法（https://blog.csdn.net/huxiaochao_6053/article/details/87623168）：
                                //合并单元格需注意假如某个单元格不需要合并则就不需要往List<Integer>集合中塞，列如诸如以下数据[2, 2, 1, 1]就是错误数据，
                                // 因为它代表一个单元格B3。B3并不需要合并，而假如执意要合并的话则该sheet.addMergedRegion(new CellRangeAddress(2, 2,1, 1));方法会报错。则将合并 2,2,1,1 去掉就不报错了。
                                if (begin != end) {
                                    CellRangeAddress callRange = new CellRangeAddress(begin, end, a, a);
                                    sheet.addMergedRegion(callRange);
                                }
                            }
                        }
                        end += 1;
                        begin = end;
                    }
                    if (i % 100 == 0) {
                        ((SXSSFSheet) sheet).flushRows();
                    }
                    index++;
                }
                for (int i = 0; i < 13; i++) {
                    if (i < 4 || i > 6) {
                        // 合并
                        if (begin != end) {
                            CellRangeAddress callRange1 = new CellRangeAddress(begin, end, i, i);
                            sheet.addMergedRegion(callRange1);
                        }
                    }
                }
            }

            FileOutputStream out = new FileOutputStream(filePath);
            sxssfWorkbook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Override
    public void exportDataNoReply(int sheetNum, String sheetTitle, String[] headers,
                                  List<MesExceptionBillzDTO> resultSend) {

        try {

            String filePath = "E:\\000temp\\test4.xlsx";
            File file = new File(filePath);
            XSSFWorkbook workbook1 = new XSSFWorkbook(new FileInputStream(file));
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);

            // 生成一个表格
//            Sheet sheet = sxssfWorkbook.createSheet();
            Sheet sheet = sxssfWorkbook.getSheetAt(0);
            // 设置sheet名字
            sxssfWorkbook.setSheetName(sheetNum, "异常问题导出");
            /* **********************第二行标题start*************************** */
            // 表格第二行标题
            Row headTitleSecond = sheet.createRow(0);
            // 设置样式
            CellStyle firstRowcellStyle = sxssfWorkbook.createCellStyle();
            firstRowcellStyle.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色
//        firstRowcellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
//        firstRowcellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//        firstRowcellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        firstRowcellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//        firstRowcellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//        firstRowcellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//        firstRowcellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            Font font = sxssfWorkbook.createFont();
            font.setFontName("宋体");
            font.setFontHeight((short) 220);
//        font.setBoldweight((short) 700);
            firstRowcellStyle.setFont(font);
            Cell cel = headTitleSecond.createCell(0);
            cel.setCellStyle(firstRowcellStyle);
            cel.setCellValue("异常类型");
            Cell cel1 = headTitleSecond.createCell(1);
            cel1.setCellStyle(firstRowcellStyle);
            cel1.setCellValue("产品项目");
            Cell ce2 = headTitleSecond.createCell(2);
            ce2.setCellStyle(firstRowcellStyle);
            ce2.setCellValue("异常标题");
            Cell cel3 = headTitleSecond.createCell(3);
            cel3.setCellStyle(firstRowcellStyle);
            cel3.setCellValue("异常内容");
//            Cell cel5 = headTitleSecond.createCell(4);
//            cel5.setCellStyle(firstRowcellStyle);
//            cel5.setCellValue("是否归零");
//            Cell cel6 = headTitleSecond.createCell(5);
//            cel6.setCellStyle(firstRowcellStyle);
//            cel6.setCellValue("发布人");
//            Cell cel7 = headTitleSecond.createCell(6);
//            cel7.setCellStyle(firstRowcellStyle);
//            cel7.setCellValue("发布时间");
//            Cell cel8 = headTitleSecond.createCell(7);
//            cel8.setCellStyle(firstRowcellStyle);
//            cel8.setCellValue("负责人");
//            Cell cel9 = headTitleSecond.createCell(8);
//            cel9.setCellStyle(firstRowcellStyle);
//            cel9.setCellValue("设计人员");
//            Cell cel10 = headTitleSecond.createCell(9);
//            cel10.setCellStyle(firstRowcellStyle);
//            cel10.setCellValue("是否有附件");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Row row = null;
            // 遍历集合数据，产生数据行
            if (resultSend != null) {
                int index = 1;
                for (int i = 0; i < resultSend.size(); i++) {
                    row = sheet.createRow(index);
                    MesExceptionBillzDTO dto = resultSend.get(i);
                    row.setHeightInPoints(22.0f);
                    row.createCell(0).setCellValue((dto.getTypeName() != null) ? dto.getTypeName() : "");
                    row.createCell(1).setCellValue((dto.getTypeNameL() != null) ? dto.getTypeNameL() : "");
                    row.createCell(2).setCellValue((dto.getExTittle() != null) ? dto.getExTittle() : "");
                    row.createCell(3).setCellValue((dto.getExDescribeInfo() != null) ? dto.getExDescribeInfo() : "");
                    if (i % 100 == 0) {
                        ((SXSSFSheet) sheet).flushRows();
                    }
                    index++;
                }
            }

            FileOutputStream out = new FileOutputStream(filePath);
            sxssfWorkbook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
